package com.yuanqi.repository;

import com.yuanqi.domain.RetailReturnOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 零售退货订单数据访问层
 */
@Repository
public interface RetailReturnOrderRepository extends JpaRepository<RetailReturnOrder, Long> {
    
    /**
     * 根据退货单编码查找
     */
    Optional<RetailReturnOrder> findByReturnCode(String returnCode);
    
    /**
     * 根据原订单ID查找退货单
     */
    List<RetailReturnOrder> findByOriginalOrderIdOrderByReturnDateDesc(Long originalOrderId);
    
    /**
     * 根据原订单编码查找退货单
     */
    List<RetailReturnOrder> findByOriginalOrderCodeOrderByReturnDateDesc(String originalOrderCode);
    
    /**
     * 根据客户ID查找退货单
     */
    List<RetailReturnOrder> findByCustomerIdOrderByReturnDateDesc(Long customerId);
    
    /**
     * 根据客户姓名查找退货单
     */
    List<RetailReturnOrder> findByCustomerNameContainingOrderByReturnDateDesc(String customerName);
    
    /**
     * 根据客户电话查找退货单
     */
    List<RetailReturnOrder> findByCustomerPhoneOrderByReturnDateDesc(String customerPhone);
    
    /**
     * 根据退货状态查找
     */
    List<RetailReturnOrder> findByReturnStatusOrderByReturnDateDesc(String returnStatus);
    
    /**
     * 根据日期范围查找退货单
     */
    @Query("SELECT rro FROM RetailReturnOrder rro WHERE rro.returnDate BETWEEN :startDate AND :endDate ORDER BY rro.returnDate DESC")
    List<RetailReturnOrder> findByReturnDateBetween(@Param("startDate") LocalDateTime startDate, 
                                                    @Param("endDate") LocalDateTime endDate);
    
    /**
     * 统计今日退货单数量
     */
    @Query("SELECT COUNT(rro) FROM RetailReturnOrder rro WHERE DATE(rro.returnDate) = CURRENT_DATE")
    Long countTodayReturns();
    
    /**
     * 统计本月退货单数量
     */
    @Query("SELECT COUNT(rro) FROM RetailReturnOrder rro WHERE YEAR(rro.returnDate) = YEAR(CURRENT_DATE) AND MONTH(rro.returnDate) = MONTH(CURRENT_DATE)")
    Long countMonthReturns();
    
    /**
     * 统计今日退货金额
     */
    @Query("SELECT COALESCE(SUM(rro.returnAmount), 0) FROM RetailReturnOrder rro WHERE DATE(rro.returnDate) = CURRENT_DATE")
    Double sumTodayReturnAmount();
    
    /**
     * 统计本月退货金额
     */
    @Query("SELECT COALESCE(SUM(rro.returnAmount), 0) FROM RetailReturnOrder rro WHERE YEAR(rro.returnDate) = YEAR(CURRENT_DATE) AND MONTH(rro.returnDate) = MONTH(CURRENT_DATE)")
    Double sumMonthReturnAmount();
    
    /**
     * 查找待审核的退货单
     */
    @Query("SELECT rro FROM RetailReturnOrder rro WHERE rro.returnStatus = 'Pending' ORDER BY rro.returnDate ASC")
    List<RetailReturnOrder> findPendingReturns();
    
    /**
     * 查找已审核的退货单
     */
    @Query("SELECT rro FROM RetailReturnOrder rro WHERE rro.returnStatus = 'Approved' ORDER BY rro.returnDate ASC")
    List<RetailReturnOrder> findApprovedReturns();
    
    /**
     * 查找处理中的退货单
     */
    @Query("SELECT rro FROM RetailReturnOrder rro WHERE rro.returnStatus = 'Processing' ORDER BY rro.returnDate ASC")
    List<RetailReturnOrder> findProcessingReturns();
    
    /**
     * 查找已完成的退货单
     */
    @Query("SELECT rro FROM RetailReturnOrder rro WHERE rro.returnStatus = 'Completed' ORDER BY rro.returnDate DESC")
    List<RetailReturnOrder> findCompletedReturns();
}
